package utils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import script.TestUser;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

//数据库操作工具
public class DBUtils {
    private static final Logger logger = LoggerFactory.getLogger(TestUser.class);

    //获取数据库连接对象
    public static Connection getConn() throws Exception {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接对象
        String url = "jdbc:mysql://8.131.75.42:3306/czbk_member?characterEncoding=UTF-8";
        return DriverManager.getConnection(url, "root", "TestDev01_p2p");
    }

    //释放资源
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void close(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void close(Connection conn, Statement statement, ResultSet resultSet) {
        close(resultSet);
        close(statement);
        close(conn);

    }

    //查询一条数据
    public static Map<String, Object> getOne(String sql) throws Exception {
        logger.info("sql={}", sql);
        Map<String, Object> result = new HashMap<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnLabel = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    result.put(columnLabel, value);
                }
            }
        } catch (Exception e) {
            //e.printStackTrace();
            logger.error("error:", e);
        } finally {
            close(conn, ps, rs);
        }
        return result;
    }
    //执行指定的sql
    public static void execute(String sql){
        logger.info("sql={}", sql);
        Connection conn=null;
        PreparedStatement preparedStatement = null;
        try {
            conn = getConn();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.execute();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(preparedStatement);
            close(conn);
        }


    }

}
